package com.jmcgg.freesky.common.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * The difference between Helper and Utility is listed below:<br>
 * Helper is the container which includes body/structure and methods<br>
 * Utility only includes methods.<br>
 * 
 * @author Jacky
 * 
 */
public class ExcelHelper extends FreeskyUtil {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(ExcelHelper.class);

	private String excelFileDir = "";
	// the first row must be HEAD ROW
	private ExcelHead head;
	private List<ExcelRow> rowList;
	private String errMsg;

	public ExcelHelper() {
	}

	public ExcelHelper(String excelFile) {
		excelFileDir = excelFile;
	}

	public void parse() {
		parse(this.excelFileDir, null, -1, 0);
	}

	private String getCellValue(Cell cell) {
		String ret = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC:// 0
			ret = String.valueOf(cell.getNumericCellValue());
			break;
		case Cell.CELL_TYPE_STRING:// 1
			ret = String.valueOf(cell.getRichStringCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:// 2
			try {
				ret = String.valueOf(cell.getNumericCellValue());
			} catch (IllegalStateException e) {
				ret = String.valueOf(cell.getRichStringCellValue());
			}
			break;
		case Cell.CELL_TYPE_BLANK:// 3
			ret = null;
			break;
		case Cell.CELL_TYPE_BOOLEAN:// 4
			ret = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR:// 5
			break;
		default:
			break;
		}
		return ret;
	}

	public void parse(String excelFile, String sheetName, int sheetIdx,
			int headRowIdx) {
		if (headRowIdx < 0) {
			errMsg = "Head Row Index is too small.";
			return;
		}
		if (!CommonUtil.isFileExist(excelFile)) {
			errMsg = "File does NOT exist!";
			return;
		}
		InputStream inp;
		try {
			inp = new FileInputStream(excelFile);
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet;

			if (sheetIdx > -1)
				sheet = wb.getSheetAt(sheetIdx);
			else if (sheetName != null)
				sheet = wb.getSheet(sheetName);
			else
				sheet = wb.getSheetAt(0);

			int rows = sheet.getPhysicalNumberOfRows();
			int cells = 0;
			if (rows > headRowIdx) {
				Row headRow = sheet.getRow(headRowIdx);
				cells = headRow.getPhysicalNumberOfCells();
				head = new ExcelHead();
				head.setCellNum(cells);
				List<String> cellList = new ArrayList<String>();
				for (int i = 0; i < cells; i++) {
					Cell cell = headRow.getCell(i);
					String cellValue = this.getCellValue(cell);
					cellList.add(cellValue);
				}
				head.setCellList(cellList);
			} else {
				errMsg = "The Sheet is empty OR the head row index is too big.";
				inp.close();
				return;
			}
			boolean isEnd = false;

			rowList = new ArrayList<ExcelRow>();
			for (int i = headRowIdx + 1; i < rows; i++) {
				Row row = sheet.getRow(i);
				if (row == null) {
					rows++;
					if (isEnd) {
						break;
					} else {
						isEnd = true;
					}
					continue;
				} else if (row.getCell(0) == null) {
					rows++;
					if (isEnd) {
						break;
					} else {
						isEnd = true;
					}
					continue;
				}
				isEnd = false;
				List<String> cellList = new ArrayList<String>();
				for (int j = 0; j < cells; j++) {
					String cellValue = this.getCellValue(row.getCell(j));
					cellList.add(cellValue);
				}
				ExcelRow excelRow = new ExcelRow();
				excelRow.setCellList(cellList);
				rowList.add(excelRow);
			}
			inp.close();
		} catch (Exception ex) {
			logger.error("parse(String, String, int)", ex);
		}
	}

	public void parseStandingData(String tableName, String resultFile) {
		this.parse();
		// first row is head row
		// second row includes code
		ExcelRow row2 = this.rowList.get(0);
		// third row is the type of column
		ExcelRow row3 = this.rowList.get(1);
		int cells = this.head.getCellNum();

		List<String> insertList = new ArrayList<String>();
		StringBuilder sqlInsertPart = new StringBuilder("INSERT INTO "
				+ tableName + " (");
		for (int j = 0; j < cells; j++) {
			if ("[SKIP]".equalsIgnoreCase(row3.getCellList().get(j))) {
				continue;
			}
			sqlInsertPart.append(row2.getCellList().get(j));
			if (j != cells - 1)
				sqlInsertPart.append(",");
		}
		StringBuilder sqlValuesPart;
		String sqlEndPart = ");";
		for (int i = 2; i < rowList.size(); i++) {
			ExcelRow row = rowList.get(i);
			sqlValuesPart = new StringBuilder(") VALUES (");
			for (int j = 0; j < cells; j++) {
				if ("String".equalsIgnoreCase(row3.getCellList().get(j))) {
					if (row.getCellList().get(j) == null)
						sqlValuesPart.append("null");
					else
						sqlValuesPart.append("'" + row.getCellList().get(j)
								+ "'");
				} else if ("Number".equalsIgnoreCase(row3.getCellList().get(j))) {
					if (row.getCellList().get(j) == null)
						sqlValuesPart.append("null");
					else
						sqlValuesPart.append(row.getCellList().get(j));
				} else if ("Integer"
						.equalsIgnoreCase(row3.getCellList().get(j))) {
					if (row.getCellList().get(j) == null)
						sqlValuesPart.append("null");
					else {
						String strNumber = row.getCellList().get(j);
						sqlValuesPart.append(TypeUtil.strNum2Int(strNumber));
					}
				} else if ("[SKIP]".equalsIgnoreCase(row3.getCellList().get(j))) {
					continue;
				}
				if (j != cells - 1)
					sqlValuesPart.append(",");
			}
			insertList.add(sqlInsertPart.toString() + sqlValuesPart.toString()
					+ sqlEndPart);
		}
		// save to result file
		CommonUtil.saveToFile(insertList, resultFile);
	}

	public String getExcelFileDir() {
		return excelFileDir;
	}

	public void setExcelFileDir(String excelFileDir) {
		this.excelFileDir = excelFileDir;
	}

	public ExcelHead getHead() {
		return head;
	}

	public void setHead(ExcelHead head) {
		this.head = head;
	}

	public List<ExcelRow> getRowList() {
		return rowList;
	}

	public void setRowList(List<ExcelRow> rowList) {
		this.rowList = rowList;
	}

	public String getErrMsg() {
		return errMsg;
	}

	public void setErrMsg(String errMsg) {
		this.errMsg = errMsg;
	}

}

class ExcelHead {

	private int cellNum;
	private List<String> cellList;

	public int getCellNum() {
		return cellNum;
	}

	public void setCellNum(int cellNum) {
		this.cellNum = cellNum;
	}

	public List<String> getCellList() {
		return cellList;
	}

	public void setCellList(List<String> cellList) {
		this.cellList = cellList;
	}
}

class ExcelRow {

	private List<String> cellList;

	public List<String> getCellList() {
		return cellList;
	}

	public void setCellList(List<String> cellList) {
		this.cellList = cellList;
	}
}
